package com.jyh.db.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import javax.websocket.Session;

import org.apache.log4j.Logger;
import org.omg.CORBA.PUBLIC_MEMBER;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.http.StreamingHttpOutputMessage;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.web.servlet.ModelAndView;

import com.alibaba.fastjson.JSONObject;
import com.jyh.config.datasource.TargetDataSource;

import net.sf.jsqlparser.statement.select.Select;

@Repository
// 持久层
public class CustomDao {
	private static Logger logger = Logger.getLogger(CustomDao.class);
	@Autowired
	private JdbcTemplate jdbcTemplate;

	/**
	 * 获取订单数据
	 * 
	 * @Title: getOverduelist
	 * @Description: TODO
	 * @param @param
	 *            request
	 * @param @return
	 * @return ModelAndView
	 * @throws @author
	 *             kiwishang
	 * @date 2017年10月19日 下午4:20:40
	 */

	public ModelAndView getOverduelist(HttpServletRequest request,HttpSession session,String orderNos,String payback) {
		//传递过来的参数
		String userName=request.getParameter("userName");
		String usercardNo=request.getParameter("usercardNo");
		String userPhone=request.getParameter("userPhone");
		String caseflge=request.getParameter("caseflge");
		String yuqiday=request.getParameter("yuqiday");
		
	    String page = request.getParameter("currentPage");// 传过来的页数
		int lineSize = 10; // 每页显示记录10条数
		int pageCount = 0; // 总页数
		int count = 0; // 数据总数
		int currentPage = 1; // 当前是第几页,默认是第一页
		if (page == null || "1".equals(page)) {
		} else {
			currentPage = Integer.parseInt(page); // 当前是第几页,默认是第一页
		}
		if (currentPage < 1) {
			currentPage = 1;
		}
		// 获取具体数据
		StringBuffer sbStringBuffer=new StringBuffer();
		sbStringBuffer.append("SELECT a.id,a.userCode,a.orderCode,a.customId,a.customName,a.userName,a.usercardNo,a.userPhone,a.csState,a.yqAmount,a.yqcostAmount,a.dqpayAmount,a.setTime,a.yqDays,case  when TIMESTAMPDIFF(DAY, a.setTime, now())>60 then 'M3' when  TIMESTAMPDIFF(DAY, a.setTime, now())>30 then 'M2' else 'M1' end as yqState,(select b.value from jyh_statu_config b where b.selectType='1' and a.hkstatu=b.key) hkstatu,a.opreateTime FROM jyh_user_order a where 1=1 ");
		//如果不是管理员
		if(!session.getAttribute("manageroleId").equals("11")){
			sbStringBuffer.append(" and customId="+session.getAttribute("userId"));
		}
		
		if(payback.equals("WH")){
			if(!orderNos.equals("")){
				sbStringBuffer.append(" and a.orderCode not in ("+orderNos+") ");
			}
		}
		if(payback.equals("HZ")){
			if(!orderNos.equals("")){
				sbStringBuffer.append(" and a.orderCode in ("+orderNos+") ");
			}
			if(orderNos.equals("")){
				sbStringBuffer.append(" and a.orderCode is null  ");
			}
		}
		if(caseflge!=null && !caseflge.equals("") && !caseflge.equals("1")){
			sbStringBuffer.append(" and a.hkstatu ='"+caseflge+"' ");
		}
		if(yuqiday!=null && !yuqiday.equals("") ){
			sbStringBuffer.append(" and a.yqDays ="+yuqiday+" ");
		}
		
		if(userName!=null && !userName.equals("") && !userName.equals("undefined")){
			sbStringBuffer.append(" and a.userName like '%"+userName+"%' ");
		}
		if(usercardNo!=null && !usercardNo.equals("") && !usercardNo.equals("undefined")){
			sbStringBuffer.append(" and a.usercardNo like '%"+usercardNo+"%' ");
		}
		if(userPhone!=null && !userPhone.equals("") && !userPhone.equals("undefined")){
			sbStringBuffer.append(" and a.userPhone like '%"+userPhone+"%' ");
		}
		sbStringBuffer.append("  limit " + (currentPage - 1) * lineSize + "," + lineSize);
		String querysql=sbStringBuffer.toString();
		List<Map<String, Object>> datalist = jdbcTemplate.queryForList(querysql);
		
		// 获取总数
		StringBuffer sbcount=new StringBuffer();
		sbcount.append("SELECT count(1) as count FROM jyh_user_order a where 1=1 ");
		//如果不是管理员
		if(!session.getAttribute("manageroleId").equals("11")){
			sbcount.append(" and customId="+session.getAttribute("userId"));
		}
		
		if(payback.equals("WH")){
			if(!orderNos.equals("")){
				sbcount.append(" and a.orderCode not in ("+orderNos+") ");
			}
		
			
		}
		if(payback.equals("HZ")){
			if(!orderNos.equals("")){
				sbcount.append(" and a.orderCode in ("+orderNos+") ");
			}
			if(orderNos.equals("")){
				sbcount.append(" and a.orderCode is null  ");
			}
			
		}
		if(caseflge!=null && !caseflge.equals("") && !caseflge.equals("1")){
			sbcount.append(" and a.hkstatu ='"+caseflge+"' ");
		}
	
		if(yuqiday!=null && !yuqiday.equals("") ){
			sbcount.append(" and a.yqDays ="+yuqiday+"");
		}
		if(userName!=null && !userName.equals("") && !userName.equals("undefined")){
			sbcount.append(" and a.userName like '%"+userName+"%' ");
		}
		if(usercardNo!=null && !usercardNo.equals("") && !usercardNo.equals("undefined")){
			sbcount.append(" and a.usercardNo like '%"+usercardNo+"%' ");
		}
		if(userPhone!=null && !userPhone.equals("") && !userPhone.equals("undefined")){
			sbcount.append(" and a.userPhone like '%"+userPhone+"%' ");
		}
		String countsql=sbcount.toString();
	    try {
			Map<String, Object> map = jdbcTemplate.queryForMap(countsql);
			count = Integer.parseInt(map.get("count").toString());
		} catch (EmptyResultDataAccessException e) {
		}

		int temp = count / lineSize; // 取商
		int tem2 = count % lineSize;// 取余数
		if (tem2 > 0 && tem2 < lineSize && temp > 0) {
			pageCount = count / lineSize + 1; // 总页数
		} else if (tem2 == 0 && temp > 0) {
			pageCount = temp;
		} else {
			pageCount = 1; // 总页数
		}
		if (currentPage >= pageCount) {
			currentPage = pageCount;
		}
         String backurl="";
		if(payback.equals("HZ")){
			backurl="riskManage/mypayback";
		}else{
			backurl="riskManage/riskmanage";
		}
		
		ModelAndView mvAndView = new ModelAndView(backurl);
		mvAndView.addObject("datalist", datalist);
		mvAndView.addObject("pageCount", pageCount);
		mvAndView.addObject("currentPage", currentPage);
		mvAndView.addObject("count", count);
		//将传递过来的参数回显
		mvAndView.addObject("userName", userName);
		mvAndView.addObject("usercardNo", usercardNo);
		mvAndView.addObject("userPhone", userPhone);
		mvAndView.addObject("yuqiday", yuqiday);
		mvAndView.addObject("caseflge", caseflge);
		
		return mvAndView;
	}

	/**
	 * 获取逾期未还款的用户的信息
	 * 
	 * @Title: getuserinfoList
	 * @Description: TODO
	 * @param @param
	 *            userno
	 * @param @return
	 * @return List<Map<String,Object>>
	 * @throws @author
	 *             kiwishang
	 * @date 2017年10月20日 下午3:50:13
	 */
	@TargetDataSource(name = "order")
	public ModelAndView getwhkorderlist(HttpServletRequest request) {
		String userName = request.getParameter("userName");
		String page = request.getParameter("currentPage");// 传过来的页数
		int lineSize = 10; // 每页显示记录10条数
		int pageCount = 0; // 总页数
		int count = 0; // 数据总数
		int currentPage = 1; // 当前是第几页,默认是第一页
		if (page == null || "1".equals(page)) {
		} else {
			currentPage = Integer.parseInt(page); // 当前是第几页,默认是第一页
		}
		if (currentPage < 1) {
			currentPage = 1;
		}

		StringBuffer sbBuffer = new StringBuffer();
		sbBuffer.append("SELECT AA.orderNO,AA.userNO,AA.overdueSumMoney,AA.yqday,AA.payMoney,AA.repayMoney,c.loginAccount,c.userName FROM ( ");
		sbBuffer.append("SELECT A.userNO,A.overdueSumMoney,A.payMoney,A.repayMoney,A.yqday ,A.orderNO FROM ( ");
		sbBuffer.append(
				"SELECT a.userNO,a.orderNO,b.overdueSumMoney,b.payMoney,b.repayMoney,TO_DAYS(NOW())-TO_DAYS(b.payTime) AS yqday FROM jyh_order a  ");
		sbBuffer.append(
				"LEFT JOIN jyh_order_payment b ON a.orderNO=b.orderNO WHERE b.payTime IS NOT NULL AND a.state='10' AND a.isCollection=0 ");
		sbBuffer.append(" ) AS A ) AA   LEFT JOIN jyh_user_account c ON c.userNO=AA.userNO where 1=1 ");
		if (userName != null && !userName.equals("") && !userName.equals("undefined")) {
			sbBuffer.append(" and c.userName like '%" + userName + "%' ");
		}
		sbBuffer.append(" limit " + (currentPage - 1) * lineSize + "," + lineSize);
		String querysql = sbBuffer.toString();
		logger.info("输出sql:" + querysql);
		List<Map<String, Object>> datalist = jdbcTemplate.queryForList(querysql);
		// 获取总数
		StringBuffer sbcountBuffer = new StringBuffer();
		sbcountBuffer.append("SELECT count(1) as countnum FROM ( ");
		sbcountBuffer.append("SELECT A.userNO,A.overdueSumMoney,A.payMoney,A.repayMoney,A.yqday ,A.orderNO FROM ( ");
		sbcountBuffer.append(
				"SELECT a.userNO,a.orderNO,b.overdueSumMoney,b.payMoney,b.repayMoney,TO_DAYS(NOW())-TO_DAYS(b.payTime) AS yqday FROM jyh_order a  ");
		sbcountBuffer.append(
				"LEFT JOIN jyh_order_payment b ON a.orderNO=b.orderNO WHERE b.payTime IS NOT NULL AND a.state='10' AND a.isCollection=0  ");
		sbcountBuffer.append(" ) AS A ) AA   LEFT JOIN jyh_user_account c ON c.userNO=AA.userNO where 1=1 ");
		if (userName != null && !userName.equals("") && !userName.equals("undefined")) {
			sbcountBuffer.append(" and c.userName like '%" + userName + "%' ");
		}
		sbcountBuffer.append(" limit " + (currentPage - 1) * lineSize + "," + lineSize);
		String countsql = sbcountBuffer.toString();
		logger.info("输出数量sql:" + countsql);
		try {
			Map<String, Object> map = jdbcTemplate.queryForMap(countsql);
			count = Integer.parseInt(map.get("countnum").toString());
		} catch (EmptyResultDataAccessException e) {
		}
		int temp = count / lineSize; // 取商
		int tem2 = count % lineSize;// 取余数
		if (tem2 > 0 && tem2 < lineSize && temp > 0) {
			pageCount = count / lineSize + 1; // 总页数
		} else if (tem2 == 0 && temp > 0) {
			pageCount = temp;
		} else {
			pageCount = 1; // 总页数
		}
		if (currentPage >= pageCount) {
			currentPage = pageCount;
		}
		ModelAndView mv = new ModelAndView("riskManage/setFenpei");
		mv.addObject("datalist", datalist);
		mv.addObject("pageCount", pageCount);
		mv.addObject("currentPage", currentPage);
		mv.addObject("count", count);

		return mv;
	}

	/**
	 * 获取客服人员列表
	 * 
	 * @Title: getcustomuser
	 * @Description: TODO
	 * @param @return
	 * @return List<Map<String,Object>>
	 * @throws @author
	 *             kiwishang
	 * @date 2017年10月23日 上午11:08:01
	 */
	public List<Map<String, Object>> getcustomuser() {
		List<Map<String, Object>> userlist = new ArrayList<Map<String, Object>>();
		String querysql = "SELECT ID as  id,manger as userName,role_id as roleId FROM jyh_manager where is_login=1 AND role_id NOT IN (11)";
		userlist = jdbcTemplate.queryForList(querysql);
		return userlist;
	}

	/**
	 * 案件分配
	 * 
	 * @Title: setTaskbyhander
	 * @Description: TODO
	 * @param @return
	 * @return int
	 * @throws @author
	 *             kiwishang
	 * @date 2017年10月23日 下午3:54:55
	 */
	@TargetDataSource(name = "order")
	public List<Map<String, Object>> setTaskbyhander(String fenpeiType,String ordernos) {
		StringBuffer sbBuffer = new StringBuffer();
		sbBuffer.append("SELECT AA.orderNO,AA.userNO,AA.overdueSumMoney,AA.payMoney,AA.repayMoney,AA.yqday,c.loginAccount,c.userName FROM ( ");
		sbBuffer.append("SELECT A.userNO,A.overdueSumMoney,A.payMoney,A.repayMoney,A.yqday ,A.orderNO FROM ( ");
		sbBuffer.append(
				"SELECT a.userNO,a.orderNO,b.overdueSumMoney,b.payMoney,b.repayMoney,TO_DAYS(NOW())-TO_DAYS(b.payTime) AS yqday FROM jyh_order a  ");
		sbBuffer.append(
				"LEFT JOIN jyh_order_payment b ON a.orderNO=b.orderNO WHERE b.payTime IS NOT NULL AND a.state='10' AND a.isCollection=0 ");
		sbBuffer.append(" ) AS A ) AA   LEFT JOIN jyh_user_account c ON c.userNO=AA.userNO where  1=1 ");
		//如果是平均分配的话
		if(fenpeiType.equals("2")){
		sbBuffer.append("   and AA.orderNO in ("+ ordernos + ")");
		}
		
		String querysql = sbBuffer.toString();
		List<Map<String, Object>> datalist = jdbcTemplate.queryForList(querysql);
		return datalist;
	}

	/**
	 * 通过usernumber查询用户身份证号
	 * 
	 * @Title: getadduserNamelist
	 * @Description: TODO
	 * @param @param
	 *            datalist
	 * @param @return
	 * @return List<Map<String,Object>>
	 * @throws @author
	 *             kiwishang
	 * @date 2017年10月23日 下午6:39:41
	 */
	@TargetDataSource(name = "user")
	public List<Map<String, Object>> getadduserNamelist(List<Map<String, Object>> datalist) {
		if (datalist != null && datalist.size() > 0) {
			for (int i = 0; i < datalist.size(); i++) {
				String idcardno = getcardno(datalist.get(i).get("userNO").toString());
				datalist.get(i).put("usercardNo", idcardno);
			}
		}
		return datalist;
	}

	/**
	 * 保存分配的数据
	* @Title: savesetdata 
	* @Description: TODO
	* @param @param datalist
	* @param @return    
	* @return int    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月23日 下午6:43:16
	 */
	public  int savesetdata(List<Map<String, Object>> datalist,String customid,String customName){
		int num=0;
	    //循环list 向客户客服关系表中插入数据
		String excuetsql="insert into jyh_user_order (userCode,orderCode,customId,customName,userName,usercardNo,userPhone,setTime,yqDays,yqState,yqAmount,yqcostAmount,dqpayAmount) values";
			StringBuffer addbuff= new StringBuffer();
			     for (int i = 0; i < datalist.size(); i++) {
			       //for循环里就是你需要处理的数据 
			       addbuff.append("('"+datalist.get(i).get("userNO").toString()+"',");
			       addbuff.append("'"+datalist.get(i).get("orderNO").toString()+"',");
			       addbuff.append("'"+customid+"',");
			       addbuff.append("'"+customName+"',");
			       addbuff.append("'"+datalist.get(i).get("userName").toString()+"',");
			       addbuff.append("'"+datalist.get(i).get("usercardNo").toString()+"',");
			       addbuff.append("'"+datalist.get(i).get("loginAccount").toString()+"',");
			       addbuff.append(" now(),");
			       addbuff.append(datalist.get(i).get("yqday")+",");
			       addbuff.append("'M1',");
			       addbuff.append("1139.00,");
			       addbuff.append(datalist.get(i).get("overdueSumMoney")+",");
			       addbuff.append(datalist.get(i).get("payMoney")+"),");
			    }
			    // 构建完整sql  
			     String addbuffstr=addbuff.toString().substring(0,addbuff.toString().length()-1);
			    String sql = excuetsql+addbuffstr;
			   num= jdbcTemplate.update(sql);
	
		return num;
	}
	/**
	 * 当分配之后 修改订单的是否催收为1
	* @Title: updateOrder 
	* @Description: TODO
	* @param @return    
	* @return int    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月24日 下午2:15:54
	 */
	@TargetDataSource(name = "order")
	public int updateOrder(String ordernos,String fenpeiType){
		String updatesql="";
		if(fenpeiType.equals("2")){
		    updatesql="update jyh_order set isCollection=1 where orderNO in ("+ordernos+")";
		}else{
			updatesql="update jyh_order set isCollection=1 where state='10'";
		} 
		int updatenum=jdbcTemplate.update(updatesql);
		return updatenum;
	}

	/**
	 * 通过用户编号获取身份证号
	 * 
	 * @Title: getcardno
	 * @Description: TODO
	 * @param @return
	 * @return String
	 * @throws @author
	 *             kiwishang
	 * @date 2017年10月23日 下午5:01:19
	 */
	@TargetDataSource(name = "user")
	public String getcardno(String userNumber) {
		String querysql = "SELECT userIDCard FROM jyh_person WHERE userNumber='" + userNumber + "'";
		List<Map<String, Object>> datalist = jdbcTemplate.queryForList(querysql);
		String idcardno = "";
		if (datalist != null && datalist.size() > 0) {
			idcardno = datalist.get(0).get("userIDCard").toString();
		}
		return idcardno;
	}
	/**
	 * 获取联系人信息
	* @Title: getcataperson 
	* @Description: TODO
	* @param @param userNumber
	* @param @return    
	* @return Map<String,Object>    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月25日 上午11:58:41
	 */
	@TargetDataSource(name = "user")
	public Map<String, Object> getcataperson(String userNumber){
		String querysql="select * from jyh_contact_info where userNumber='"+userNumber+"'";
		List<Map<String, Object>> datelist=jdbcTemplate.queryForList(querysql);
		Map<String, Object> datamap =new HashMap<String,Object>();
		if(datelist!=null && datelist.size()>0){
			datamap=datelist.get(0);
		}
		return datamap;
	}
	
	/**
	 * 获取个人基本信息
	* @Title: getuserinfo 
	* @Description: TODO
	* @param @param userNumber
	* @param @return    
	* @return Map<String,Object>    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月25日 下午3:15:52
	 */
	@TargetDataSource(name = "order")
	public Map<String, Object> getuserinfo(HttpServletRequest request){
	    String orderno=request.getParameter("orderno");
	    String userPhone=request.getParameter("userPhone");
	    Map<String, Object> userinfomap=new HashMap<String,Object>();
		//获取借款时的地址
		String querysql="SELECT * FROM jyh_user_msg WHERE orderNo='"+orderno+"' ";
		List<Map<String, Object>> infolist=jdbcTemplate.queryForList(querysql);
		if(infolist!=null&& infolist.size()>0){
			userinfomap.put("userName", infolist.get(0).get("userName"));	
			userinfomap.put("sex", infolist.get(0).get("sex"));
			userinfomap.put("age", infolist.get(0).get("age"));
			userinfomap.put("address", infolist.get(0).get("address"));
			userinfomap.put("userPhone", userPhone);
		}
		return userinfomap;
	}
	/**
	 * 获取该订单的具体信息
	* @Title: getOrderInfo 
	* @Description: TODO
	* @param @param request
	* @param @return    
	* @return Map<String,Object>    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月25日 下午3:42:26
	 */
	@TargetDataSource(name = "order")
	public Map<String, Object> getOrderInfo(String  orderno){
		  Map<String, Object> orderinfomap=new HashMap<String,Object>();
		  String querysql="select A.orderNO, (A.payMoney-A.overdueSumMoney) as totalmoney,A.overdueSumMoney,A.repayMoney,A.payMoney,case when  A.executeTime is null then TO_DAYS(now())-TO_DAYS(A.payTime) else   TO_DAYS(A.executeTime)-TO_DAYS(A.payTime) end  AS yqday  from jyh_order_payment A inner join "+ 
                          " (select a.orderNo,b.rateSum,b.serviceMoney from jyh_order a inner join jyh_loan_type b on a.typeNO=b.typeNO ) B "+
                          " on A.orderNO=B.orderNO where A.orderNo='"+orderno+"'";
		  try{
		  orderinfomap=jdbcTemplate.queryForMap(querysql);
		  }catch(Exception e){e.printStackTrace();}
		  return orderinfomap;
	}
	/**
	 * 获取案件标签
	* @Title: getcaselist 
	* @Description: TODO
	* @param @return    
	* @return List<Map<String,Object>>    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月25日 下午6:22:51
	 */
	public List<Map<String, Object>> getcaselist(String  selectType){
		String querysql="select * from jyh_statu_config where selectType='"+selectType+"'";
		List<Map<String, Object>> listdate=jdbcTemplate.queryForList(querysql);
		return listdate;
		}
	/**
	 * 设置标签
	* @Title: setcaseflag 
	* @Description: TODO
	* @param @return    
	* @return int    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月26日 下午5:30:55
	 */
	public int setcaseflag(String orderno,String caseflag){
		String updatesql="update jyh_user_order set hkstatu='"+caseflag+"' where orderCode='"+orderno+"'";
		int updatenum=jdbcTemplate.update(updatesql);
		//修改操作时间
		updataOperateTime(orderno);
		return updatenum;
	}
	/**
	 * 保存催收记录
	* @Title: saveCSinfo 
	* @Description: TODO
	* @param @param request
	* @param @return    
	* @return int    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月26日 下午6:14:23
	 */
	public String saveCSinfo(HttpServletRequest request){
		   String showtype=request.getParameter("showtype");
		   String orderno=request.getParameter("orderno");
	       String csuserphone=request.getParameter("csuserphone");
	       String callcontentm=request.getParameter("callcontentm");
	       String relation=request.getParameter("relation");
	       String relationname=request.getParameter("relationname");
	       String cscontent=request.getParameter("cscontent");
	       String insertsql="insert into jyh_cs_info_detail (bjUserPhone,callMessage,userrelation,relationName,remake,orderno,opreateTime) values"+
	                           "('"+csuserphone+"','"+callcontentm+"','"+relation+"','"+relationname+"','"+cscontent+"','"+orderno+"',now())";
	       if(showtype.equals("2")){
	    	   jdbcTemplate.execute(insertsql);  
	       }
	        List<Map<String, Object>> dataliat=jdbcTemplate.queryForList("select a.bjUserPhone,(select value from jyh_statu_config b where b.key=a.callMessage and b.selectType='2') callMessage,a.userrelation,a.relationName,a.remake,a.orderno,a.opreateTime from jyh_cs_info_detail a where a.orderno='"+orderno+"'");
	        //修改操作时间
			updataOperateTime(orderno);
	        JSONObject reposemap=new JSONObject();
	       reposemap.put("resultdata", dataliat);
		   return reposemap.toJSONString();
	}

	
	/**
	 * 保存发送的信息
	* @Title: saveMessage 
	* @Description: TODO
	* @param @return    
	* @return int    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月27日 上午10:39:04
	 */
	public List<Map<String, Object>> saveMessage(String smsStr,String userTel,String orderno,String sendtype){
		if(sendtype.equals("2")){
		String  insertsql="insert into jyh_cs_message(messageContent,touserPhone,opreateTime,orderno) values "+
	                      "('"+smsStr+"','"+userTel+"',now(),'"+orderno+"')";
		int isertnum=jdbcTemplate.update(insertsql);
		}
		String querysql="select * from jyh_cs_message where orderno='"+orderno+"'";
		List<Map<String, Object>> datalist=new ArrayList<Map<String,Object>>();
		datalist=jdbcTemplate.queryForList(querysql);
		//修改操作时间
		updataOperateTime(orderno);
		return datalist;
	}
	
	/**
	 * 保存申请留案
	* @Title: saveReplay 
	* @Description: TODO
	* @param @param request
	* @param @return    
	* @return String    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月27日 上午11:10:39
	 */
	public String saveReplay(HttpServletRequest request,String customName){
		String orderno=request.getParameter("orderno");
		String savetype=request.getParameter("savetype");
		String replayDays=request.getParameter("replayDays");
		String replayContent=request.getParameter("replayContent");
		if(savetype.equals("2")){
		String insertsql="insert into jyh_cs_replaycase(sqTime,sqUser,sqContent,orderno,sqDays) values "+
		                 "(now(),'"+customName+"','"+replayContent+"','"+orderno+"','"+replayDays+"')";
		jdbcTemplate.update(insertsql);
		}
		String querysql="select * from jyh_cs_replaycase where orderno='"+orderno+"'";
		List<Map<String, Object>> datalist=new ArrayList<Map<String,Object>>();
		datalist=jdbcTemplate.queryForList(querysql);
		//修改操作时间
		updataOperateTime(orderno);
		JSONObject reposemap=new JSONObject();
	    reposemap.put("resultdata", datalist);
	    return reposemap.toJSONString();
		
	}
	
	/**
	 * 修改操作时间
	* @Title: updataOperateTime 
	* @Description: TODO
	* @param @return    
	* @return int    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月27日 下午4:28:34
	 */
	public int updataOperateTime(String orderno){
		String updatasql="update jyh_user_order set opreateTime=now() where orderCode='"+orderno+"'";
		int updatenum=jdbcTemplate.update(updatasql);
		return updatenum;
	}
	
	/**
	 * 获取已经还清的订单
	* @Title: getPaybackorder 
	* @Description: TODO
	* @param @return    
	* @return List<Map<String,Object>>    
	* @throws 
	* @author kiwishang 
	* @date 2017年10月31日 下午5:32:58
	 */
	@TargetDataSource(name = "order")
	public List<Map<String, Object>> getPaybackorder(){
		String querysql="SELECT orderNo FROM jyh_order WHERE state=9 AND isCollection=1";
		List<Map<String, Object>> ordernolist=jdbcTemplate.queryForList(querysql);
		return ordernolist;
	}
	
	/**
	 * 获取通话时长
	* @Title: getphoneContent 
	* @Description: TODO
	* @param @param userNumber
	* @param @return    
	* @return List<Map<String,Object>>    
	* @throws 
	* @author kiwishang 
	* @date 2017年11月29日 下午4:43:47
	 */
	@TargetDataSource(name = "three")
	public List<Map<String, Object>> getphoneContent(String userNumber){
		StringBuffer sbStringBuffer=new StringBuffer();
		sbStringBuffer.append(" SELECT A.call_number,SUM(A.calltime)/60 calltimes FROM ");
		sbStringBuffer.append(" (SELECT DISTINCT(call_number),(duration)AS calltime" );
		sbStringBuffer.append(" FROM jyh_keep_sp_bill_calling_info WHERE user_no = '"+userNumber+"' ");
		sbStringBuffer.append(" ORDER BY calltime DESC ) A  GROUP  BY A.call_number ORDER BY calltimes DESC LIMIT 0,30");
		String sql=sbStringBuffer.toString();
		List<Map<String, Object>> datalist = jdbcTemplate.queryForList(sql);
		return datalist;

	}
	/**
	 * 获取回款统计
	* @Title: getTotalMoney 
	* @Description: TODO
	* @param @param request
	* @param @return    
	* @return String    
	* @throws 
	* @author kiwishang 
	* @date 2017年12月4日 下午4:19:57
	 */
	@TargetDataSource(name = "order")
	public String getTotalMoney(HttpServletRequest request){
		String start_time=request.getParameter("start_time");
		String end_time=request.getParameter("end_time");
		String orderNos="";
		String totalmoney="";
		List<Map<String, Object>> ordernolist=getPaybackorder();
		if(ordernolist!=null && ordernolist.size()>0){
			for(int i=0;i<ordernolist.size();i++){
				orderNos+="'"+ordernolist.get(i).get("orderNo")+"',";
		}
			orderNos=orderNos.substring(0,orderNos.length()-1);
			
	    }
		 List<Map<String, Object>> orderinfomap=new ArrayList<Map<String, Object>>();
		 StringBuffer sbStringBuffer=new StringBuffer();
		 sbStringBuffer.append("SELECT SUM(A.payMoney) AS totalmoney FROM jyh_order_payment A ");
		 sbStringBuffer.append(" INNER JOIN (SELECT a.orderNo FROM jyh_order a INNER JOIN jyh_loan_type b ON a.typeNO = b.typeNO WHERE a.state=9) B ON A.orderNO = B.orderNO WHERE A.orderNo IN (");
		 sbStringBuffer.append(orderNos+")");
		 if (start_time != null && !start_time.equals("") ) {
			 sbStringBuffer.append(" and A.executeTime>='"+start_time+"'");
			}
		 if (end_time != null && !end_time.equals("") ) {
			 sbStringBuffer.append(" and A.executeTime<='"+end_time+"'");
			}
		 String querysql=sbStringBuffer.toString();
		 orderinfomap=jdbcTemplate.queryForList(querysql);
		 if(orderinfomap!=null && orderinfomap.size()>0){
			 Map<String, Object> map= orderinfomap.get(0);
			 totalmoney=map.get("totalmoney").toString();
		 }
		
		 return totalmoney;
	}
	/**
	 * 获取本地案件
	* @Title: getLocalinfo 
	* @Description: TODO
	* @param @return    
	* @return List<Map<String,Object>>    
	* @throws 
	* @author kiwishang 
	* @date 2017年12月4日 下午5:29:05
	 */
	public List<Map<String, Object>> getLocalinfo(){
		String  querysql="Select * from jyh_user_order";
		List<Map<String, Object>> datalist=new ArrayList<Map<String,Object>>();
		datalist=jdbcTemplate.queryForList(querysql);
		return datalist;
	}
	
	/**
	 * 修改本地信息
	* @Title: updateLocalMoney 
	* @Description: TODO
	* @param     
	* @return void    
	* @throws 
	* @author kiwishang 
	* @date 2017年12月4日 下午5:41:39
	 */
	public void updateLocalMoney(String orderCode,String yqDays,String yqcostAmount,String dqpayAmount){
		String updatsql="update jyh_user_order set yqDays="+yqDays+",yqcostAmount="+yqcostAmount+",dqpayAmount="+dqpayAmount+" where orderCode='"+orderCode+"'";
		jdbcTemplate.update(updatsql);
		
	}
	/**
	 * 获取所有的催记
	* @Title: getCSallinfo 
	* @Description: TODO
	* @param @return    
	* @return List<Map<String,Object>>    
	* @throws 
	* @author kiwishang 
	* @date 2017年12月11日 上午10:23:45
	 */
	public List<Map<String, Object>> getCSallinfo(String orderno){
		String querysql="SELECT CONCAT(opreateTime,'  ',remake) remakes FROM `jyh_cs_info_detail` WHERE orderno='"+orderno+"' AND remake IS NOT NULL AND remake!=''";
		List<Map<String, Object>> datalist=jdbcTemplate.queryForList(querysql);
		return datalist;
	}
	
}
